﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_ProjectPoleAges')
	BEGIN
		DROP PROCEDURE up_Report_ProjectPoleAges
	END

GO

CREATE PROCEDURE up_Report_ProjectPoleAges
	@ProjectId int
AS
	

DECLARE @ProjectType int
SELECT @ProjectType = ProjectTypeFk FROM tb_Vendor_Projects WHERE Vendor_ProjectId = @ProjectId

-- Project Type 1 is Xmission Inspection Visit
IF (@ProjectType = 1)
BEGIN
	
	SELECT 
		 PoleView.PoleAge
		,Count(PoleView.CountPoles) as Count
	FROM
	(
		--Define view
		SELECT
			1 as CountPoles,
			PoleAge = 
				CASE
					WHEN DATEPART(year,getdate()) - P.Year <= 10 THEN 'Less than 10'
					WHEN DATEPART(year,getdate()) - P.Year > 10 THEN 'More than 10'
				END
		FROM tb_Vendor_InspVisit_XmissionStructures_Poles P
		WHERE Vendor_ProjectFk = @ProjectId			
	) as PoleView
	WHERE NOT PoleView.PoleAge IS NULL
	GROUP BY PoleView.PoleAge
END

-- Project Type 2 is Xmission GPS Visit
IF (@ProjectType = 2)
BEGIN
	SELECT 
		 PoleView.PoleAge
		,Count(PoleView.CountPoles) as Count
	FROM
	(
		--Define view
		SELECT
			1 as CountPoles,
			PoleAge = 
				CASE
					WHEN DATEPART(year,getdate()) - P.Year <= 10 THEN 'Less than 10'
					WHEN DATEPART(year,getdate()) - P.Year > 10 THEN 'More than 10'
				END
		FROM tb_Vendor_Visit_XMissionStructures_Poles P
			
	) as PoleView
	WHERE NOT PoleView.PoleAge IS NULL
	GROUP BY PoleView.PoleAge
END


-- Project Type 3 is Distribution Inspection Visit
IF (@ProjectType = 3)
BEGIN

	SELECT 
		 PoleView.PoleAge
		,Count(PoleView.CountPoles) as Count
	FROM
	(
		--Define view
		SELECT
			1 as CountPoles,
			PoleAge = 
				CASE
					WHEN DATEPART(year,getdate()) - P.Year <= 10 THEN 'Less than 10'
					WHEN DATEPART(year,getdate()) - P.Year > 10 THEN 'More than 10'
				END
		FROM tb_Vendor_Distribution_Poles P
		WHERE ProjectFk = @ProjectId			
	) as PoleView
	WHERE NOT PoleView.PoleAge IS NULL
	GROUP BY PoleView.PoleAge
END

 
-- Project Type 4 is Distribution Gps Visit	
	
	




GO

GRANT EXEC ON up_Report_ProjectPoleAges TO PPIReports

GO

